﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using System.Collections;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using Agresso.Interface.Layer.Domain;
using System.Data;

namespace Agresso.Interface.Layer.Service
{
    public class BunkerPOService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        AlgBatcInputService m_AlgBatcInputService;
        agldimvalueService m_agldimvalueService;
        asuheaderService m_asuheaderService;
        AlertService m_alertService;
        VesselService m_vesselService;
        SupplierCustomerService m_suppCusService;
        ProfitCentreService m_profitCentreService;
        GradeAccountMappingService m_gradeAccountMappingService;
        ConfigurationSettingService m_configSettingService;
        ud_algbatchinput_textService m_ud_algbatchinput_textService;
        AstwarehouseService m_AstwarehouseService;
        ResponsibleUserService m_ResponsibleUserService;
        BunkerInventoryManagerMappingService m_BunkerInventoryManagerMappingService;
        apodettextService m_apodettextService;

        public BunkerPOService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_AlgBatcInputService = new AlgBatcInputService();
            m_agldimvalueService = new agldimvalueService();
            m_asuheaderService = new asuheaderService();
            m_alertService = new AlertService();
            m_suppCusService = new SupplierCustomerService();
            m_profitCentreService = new ProfitCentreService();
            m_vesselService = new VesselService();
            m_gradeAccountMappingService = new GradeAccountMappingService();
            m_configSettingService = new ConfigurationSettingService();
            m_ud_algbatchinput_textService = new ud_algbatchinput_textService();
            m_AstwarehouseService = new AstwarehouseService();
            m_ResponsibleUserService = new ResponsibleUserService();
            m_BunkerInventoryManagerMappingService = new BunkerInventoryManagerMappingService();
            m_apodettextService = new apodettextService();
        }
        public void ExecuteBunkerProcurementPO()
        {
            try
            {
                IList enquiryList = new ArrayList();

                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();

                SqlCommand cmd = new SqlCommand(BunkerPOSQL.GetAllBunkerPOOutstandingEnquiryInterface(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    string enqNo = rd["enquiry_no"].ToString();

                    SqlCommand headr = new SqlCommand(BunkerPOSQL.GetBunkerFirstPOItemStemByEnquiryNo(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                    headr.Parameters.Clear();
                    headr.Parameters.Add("@enquiry_no", SqlDbType.NVarChar).Value = enqNo;
                    SqlDataReader rdheadr = headr.ExecuteReader();
                    BunkerPO po = null;
                    rdheadr.Read();
                    po = new BunkerPO(rdheadr, false);
                    rdheadr.Close();

                    SqlCommand detils = new SqlCommand(BunkerPOSQL.GetAllBunkerPOItemStemQuotationByEnquiryNo(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                    detils.Parameters.Clear();
                    detils.Parameters.Add("@enquiry_no", SqlDbType.NVarChar).Value = enqNo;
                    SqlDataReader rddetils = detils.ExecuteReader();
                    int i = 1;
                    while (rddetils.Read())
                    {
                        BunkerPO podetil = new BunkerPO(rddetils, true);
                        podetil.ItemLineNumber = i;
                        po.Items.Add(podetil);
                        i++;
                    }
                    rddetils.Close();
                    //short info------------
                    detils.CommandText = BunkerPOSQL.GetAllBunkerPOItemAllQuotationByEnquiryNo();
                    detils.Parameters.Clear();
                    detils.Parameters.Add("@enquiry_no", SqlDbType.NVarChar).Value = enqNo;
                    rddetils = detils.ExecuteReader();
                    while (rddetils.Read())
                    {
                        BunkerPO podetil = new BunkerPO(rddetils, true);
                        string info = podetil.quotation_id + ";" + podetil.supplier_quotation + ";" + podetil.grade + ";" + podetil.price_per_mt +
                            ";" + (podetil.delivery_lumpsum_price == 0 ? "0" : podetil.delivery_lumpsum_price.ToString()) + ";";
                        po.short_info += info;
                        po.ItemsAndQuotation.Add(podetil);
                    }
                    rddetils.Close();
                    //short info-----------
                    enquiryList.Add(po);
                }
                rd.Close();

                //int test = 0;
                foreach (BunkerPO po in enquiryList)
                {
                    //  if (test == 5) break;
                    // if (po.enquiry_no.Trim() != "161") continue;
                    int order_id = GetSequenceRunningNumber();
                    string alert = "";
                    if (po.Items.Count == 0) { alert += string.Format("-[E020] Unable to find PO Detail \r\n"); }
                    //agldimvalue agrVessel = m_agldimvalueService.GetAglDimValueVesselByDescription(po.vessel);
                    po.Supplier = m_suppCusService.GetSupplierMapping(po.supplier);//, po.enquiry_no.Trim() + "-" + po.supplier.Trim(), AlertType.SOFTMAR_BUNKER_PROCUREMENT_PO);
                    //m_asuheaderService.GetSupplierByName(po.supplier);
                    po.ProfitCentre = m_profitCentreService.GetProfitCentreMapping(po.profit_centre_fullname);//, po.enquiry_no.Trim() + "-" + po.profit_centre_fullname.Trim(), AlertType.SOFTMAR_BUNKER_PROCUREMENT_PO);
                    //m_agldimvalueService.GetAglDimValueProfitCentreByDescription(po.profit_centre_fullname);
                    //bool validVessel = validateVessel(ci, agrVessel, AlertType.SOFTMAR_COMMISSION_INVOICE_TCRELET);
                    // bool validSupplierHeader = validateSupplier(po, AlertType.SOFTMAR_BUNKER_PROCUREMENT_PO);
                    // bool validProfitCentreHeader = validateProfitCentre(po, AlertType.SOFTMAR_BUNKER_PROCUREMENT_PO);
                    m_vesselService.FindVesselByName(po.vessel.Trim(), po.vessel_code, po.profit_centre_fullname); // vessel middel layer validation
                    po.Vessel = m_vesselService.GetVesselMappingByProfitCentreIDAndVesselName(po.ProfitCentre.profit_centre_id, po.vessel.Trim(), po.ProfitCentre.client);
                    // agldimvalue legal_entity = null;
                    //ResponsibleUser user = m_ResponsibleUserService.GetResponsibleUserByName(po.operated_by);
                    ResponsibleUser responsible2 = m_ResponsibleUserService.GetResponsibleUserByName(po.operated_by);
                    // if (po.Vessel != null)
                    //  legal_entity = m_agldimvalueService.GetAglDimValueLegalEntityByCostCentreDimVal(po.Vessel.agr_rel_value);
                    BunkerInventoryManagerMapping biMap = null;
                    if (po.ProfitCentre != null)
                    {
                        biMap = m_BunkerInventoryManagerMappingService.SearchInsert(po.profit_centre_fullname,
                        po.ProfitCentre.client, po.ProfitCentre.agr_description, po.ProfitCentre.agr_dim_value, po.ProfitCentre.agr_rel_value);
                    }

                    if (po.ProfitCentre.agr_dim_value == "") { alert += string.Format("-[E004] Unable to find Dim_Val for Profit Centre '{0}' \r\n", po.ProfitCentre.full_name); }
                    if (po.ProfitCentre.client == "") { alert += string.Format("-[E007] Unable to find client for Profit Centre '{0}' \r\n", po.ProfitCentre.full_name); }
                    if (po.Supplier.agr_supp_apar_id == "") { alert += string.Format("-[E005] Unable to find apar_ID for Company '{0}' \r\n", po.Supplier.full_name); }
                    if (po.Supplier.agr_supp_apar_id != "" && !(po.Supplier.agr_supp_apar_id.Substring(0, 1) != "T" || po.Supplier.agr_supp_apar_id.Substring(0, 1) != "S"))
                    {
                        alert += string.Format("-[E021] apar_ID for Company '{0}' is not T or S \r\n", po.Supplier.full_name);
                    }
                    if (po.Vessel == null) { alert += string.Format("-[E015] Unable to find Vessel '{0}' under Profit Centre '{1} {2}' \r\n", po.vessel.Trim(), po.ProfitCentre.full_name, po.ProfitCentre.agr_dim_value); }
                    if (po.Vessel != null && po.Vessel.agr_dim_value == "") { alert += string.Format("-[E010] Unable to find Dim_Val Code for Vessel '{0}' \r\n", po.vessel); }
                    //if (po.Vessel != null && legal_entity == null) { alert += string.Format("- Unable to find Legal Entity Vessel '{0}' \r\n", po.vessel.Trim()); }
                    //28082013 if (po.Vessel != null && po.Vessel.agr_dim_value.Trim() != "")
                    //28082013 {
                    //28082013     bool registered = m_AstwarehouseService.IsRegisteredWarehouse(po.Vessel.agr_dim_value);
                    //28082013     if (!registered)
                    //28082013         alert += string.Format("-[E018] Unable to find Warehouse Vessel '{0}'  \r\n", po.Vessel.agr_dim_value);
                    //28082013 }
                    if (biMap != null && (biMap.dim_value_map.Trim() == "" || biMap.rel_value_map.Trim() == ""
                        || biMap.client_map.Trim() == "")) { alert += string.Format("-[E027] Bunker Inventory Manager Mapping is not set '{0}' \r\n", po.profit_centre_fullname); }

                    foreach (BunkerPO item in po.Items)
                    {
                        item.GradeAccountMapping = m_gradeAccountMappingService.GetGradeAccountMapping(item.grade);
                        if (item.GradeAccountMapping.account == "") { alert += string.Format("-[E019] Unable to find Grade Mapping Item '{0}' \r\n", item.grade); }
                    }

                    if (alert != "")
                    {
                        foreach (BunkerPO poItem in po.ItemsAndQuotation)
                        {
                            UpdateRemarks(poItem.po_interface_id, alert, poItem.remarks);
                        }
                        //  m_alertService.CheckAndUpdateAlert(po.po_interface_id.ToString(), AlertType.SOFTMAR_BUNKER_PROCUREMENT_PO, AlertStatus.PENDING,
                        //      string.Format("Failed while interfacing PO No.{0}, PO enquiry Date :{2}, Supplier Name : {1}  \r\n", po.enquiry_no, po.supplier, po.enquiry_date.ToString("dd-MMM-yyyy")) + alert, po.enquiry_no);
                        continue;
                    }
                    //   else
                    //   {
                    //m_alertService.CheckAndUpdateAlert(po.po_interface_id.ToString(), AlertType.SOFTMAR_BUNKER_PROCUREMENT_PO, AlertStatus.RESOLVED,
                    //              string.Format("Failed while interfacing PO No.{0}, PO enquiry Date :{2}, Supplier Name : {1}  \r\n", po.enquiry_no, po.supplier, po.enquiry_date.ToString("dd-MMM-yyyy")) + alert, po.enquiry_no);
                    //   }
                    //if (po.Supplier.IsValidSupplier() && po.ProfitCentre.IsValidProfitCentre()
                    //    && po.IsAllItemIsValid)
                    //{
                    // continue;

                    IList algbatchinputList = new ArrayList();
                    algbatchinput algHeader = new algbatchinput();
                    string batchID = "SP" + DateTime.Now.ToString("ddMMyyyyHHmmss");

                    algHeader.trans_type = "41";
                    algHeader.voucher_type = "SP";
                    algHeader.dim_value_1 = biMap.rel_value_map;//18062013 legal_entity.rel_value;//14052013 po.Vessel.agr_rel_value;//Carina 14 Apr 2013
                    algHeader.dim_value_2 = "";//16052013 po.Vessel.agr_dim_value;//Carina 14 Apr 2013
                    algHeader.dim_value_5 = po.Vessel.agr_dim_value;
                    algHeader.dim_value_6 = "";//14052013carina legal_entity.dim_value; // po.ProfitCentre.agr_dim_value;
                    algHeader.dim_6 = "";//29042013carina legal_entity.dim_value; // po.ProfitCentre.agr_dim_value;
                    algHeader.apar_id = po.Supplier.agr_supp_apar_id;
                    algHeader.dim_value_5 = po.Vessel.agr_dim_value;
                    //algHeader.dim_value_4 = po.Vessel.agr_dim_value + "-V" + po.voyage_no.PadLeft(3, '0');//remove Carina 14 Apr 2013
                    algHeader.order_date = new DateTime(po.stemmed_date.Year, po.stemmed_date.Month, po.stemmed_date.Day);//LEEHOON 24042014 po.stemmed_date;
                    algHeader.ext_ord_ref = po.enquiry_no + "_" + po.enquiry_date.ToString("ddMMyyyy");
                    algHeader.cur_amount = 0;
                    //algHeader.sup_article
                    //algHeader.unit_code = po.delivery_lumpsum_unit;
                    //algHeader.unit_price = po.price_gross;
                    //algHeader.order_line
                    algHeader.line_no = 0;// po.ItemLineNumber;
                    //algHeader.value_1 = po.quantity_stemmed;
                    //algHeader.sup_article = po.delivery_lumpsum;
                    // algHeader.unit_code = po.delivery_lumpsum_unit;
                    algHeader.short_info = po.short_info.Length > 60 ? po.short_info.Substring(0, 60) : po.short_info;
                    algHeader.deliv_addr = po.port;
                    algHeader.deliv_date = po.delivery_date;
                    algHeader.accountable = "";//16072013 user.agresso_user;//po.operated_by;
                    algHeader.responsible = "DESL";//16072013 user.agresso_user;// "Carina";// po.buyer;
                    algHeader.responsible2 = responsible2.agresso_user; //po.operated_by;//16072013 user.agresso_user;// "Carina";//Carina 14 Apr 2013
                    algHeader.status = "N";
                    algHeader.client = biMap.client_map;//"S1";//09062013 legal_entity.client;//po.ProfitCentre.client;                    
                    algHeader.order_id = order_id;
                    algHeader.batch_id = batchID;
                    algHeader.order_type = "VK";
                    algHeader.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));

                    algbatchinputList.Add(algHeader);

                    // m_AlgBatcInputService.Insert(algHeader);
                    //   m_AlgBatcInputService.InsertToInterfaceTable(algHeader);
                    //  UpdateStatusPOInterfaceToDone(po.po_interface_id, algHeader.agrtid.ToString());

                    foreach (BunkerPO item in po.Items)
                    {
                        algbatchinput algDetil = new algbatchinput();
                        algDetil.trans_type = "41";
                        algDetil.voucher_type = "SP";
                        algDetil.account = "2904";//08072013 "2100";// "2904"; remove 2904 Carina 14 Apr 2013
                        //algDetil.apar_id = po.Supplier.agr_supp_apar_id;
                        algDetil.dim_1 = biMap.rel_value_map;//18062013 legal_entity.rel_value;//14052013carina po.Vessel.agr_rel_value;//Carina 14 Apr 2013
                        algDetil.dim_2 = "3334";//08072013 po.Vessel.agr_dim_value;//Carina 14 Apr 2013
                        //algDetil.dim_value_1 = po.Vessel.agr_rel_value;//Carina 14 Apr 2013legal_entity.rel_value; //cost centre legal entity
                        //algDetil.dim_value_2 = item.GradeAccountMapping.account; //item code
                        algDetil.dim_5 = po.Vessel.agr_dim_value;
                        //19062013 algDetil.dim_6 = biMap.dim_value_map;//18062013 legal_entity.dim_value; 
                        // algDetil.dim_4 = po.Vessel.agr_dim_value + "-V" + po.voyage_no.PadLeft(3, '0');//remove Carina 14 Apr 2013
                        //23122013 algDetil.tax_system = po.Supplier.country_code_sup == "SG" ? "ZI" : "0"; //05092013
                        algDetil.dim_6 = biMap.dim_value_map;//08072013
                        algDetil.dim_7 = po.ProfitCentre.agr_dim_value;//08072013
                        algDetil.order_date = new DateTime(po.stemmed_date.Year, po.stemmed_date.Month, po.stemmed_date.Day); //LEEHOON 24042014 po.stemmed_date;
                        algDetil.ext_ord_ref = po.enquiry_no + "_" + po.enquiry_date.ToString("ddMMyyyy");// po.enquiry_no + po.enquiry_date.ToString("yyyyMMdd");
                        algDetil.sup_article = item.GradeAccountMapping.account;
                        algDetil.art_descr = item.grade;
                        algDetil.article = item.GradeAccountMapping.account;
                        algDetil.unit_code = "TON";
                        algDetil.unit_price = item.price_gross;
                        algDetil.line_no = item.ItemLineNumber;
                        algDetil.value_1 = item.est_qty_max; //item.quantity_stemmed; 16052014
                        algDetil.deliv_date = po.delivery_date;
                        algDetil.order_id = order_id;
                        algDetil.amount = item.quantity_stemmed * item.price_gross;
                        algDetil.amount_set = 1;
                        algDetil.currency = "USD";//29042013Carina "SGD";
                        algDetil.cur_amount = item.price_gross * item.quantity_stemmed;
                        //algDetil.unit_code = item.delivery_lumpsum_unit;
                        //algDetil.unit_price = item.delivery_lumpsum_price == 0 ? po.price_gross : item.delivery_lumpsum_price;//if unit price item = 0 then take price from po.
                        //string sup = item.supplier.Length > 25 ? item.supplier.Substring(0, 25) : item.supplier;
                        //algDetil.short_info = item.quotation_id + ";" + item.supplier + ";" + item.grade + ";" + item.price_per_mt + ";" + item.GradeAccountMapping.account;// item.delivery_lumpsum;
                        //algDetil.deliv_addr = item.port;
                        //algDetil.accountable = item.buyer;
                        algDetil.responsible = "DESL";//16072013 user.agresso_user;// "Carina";
                        algDetil.responsible2 = responsible2.agresso_user;//po.operated_by;//16072013 user.agresso_user;
                        algDetil.status = "N";
                        algDetil.client = biMap.client_map;//"S1";//09062013 legal_entity.client;//po.ProfitCentre.client;
                        algDetil.batch_id = batchID;
                        algDetil.order_type = "VK";
                        algDetil.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                        algDetil.warehouse = "";//08072013 po.Vessel.agr_dim_value;
                        //algDetil.responsible2 = item.buyer;
                        //incase no intercompany 08072013
                        if (algDetil.dim_6 == algDetil.dim_7)
                        {
                            algDetil.dim_7 = "";//08072013
                            algDetil.account = "3334";//08072013
                            algDetil.dim_2 = "";//08072013
                        }
                        //----------------new request-------------17092013
                        if (po.Supplier.country_code_sup.Trim() == "SG") //INSIDE SINGAPORE
                        {
                            if (algDetil.dim_7.Trim() == "")//without DIM7
                            {
                                if (algDetil.dim_6.Trim() == "610" || algDetil.dim_6.Trim() == "602")
                                {
                                    algHeader.tax_code = "ZI";
                                    algHeader.tax_system = "";
                                    algDetil.tax_code = "0";
                                    algDetil.tax_system = "";
                                }
                                else if (algDetil.dim_6.Trim() == "501")
                                {
                                    algHeader.tax_code = "0";
                                    algHeader.tax_system = "";
                                    algDetil.tax_code = "0";
                                    algDetil.tax_system = "";
                                }
                            }
                            else//with DIM7 //2904
                            {
                                if (algDetil.dim_6.Trim() == "610" || algDetil.dim_6.Trim() == "602" || algDetil.dim_6.Trim() == "501")
                                {

                                    algHeader.tax_code = "0";
                                    algHeader.tax_system = "";
                                    if (po.ProfitCentre.country == "SG")
                                    {
                                        algDetil.tax_code = "0";
                                        algDetil.tax_system = "ZI";
                                    }
                                    else
                                    {
                                        algDetil.tax_code = "0";
                                        algDetil.tax_system = "";
                                    }
                                    //need mapping
                                }

                            }
                        }
                        else //OUT SIDE SINGAPORE----------------
                        {
                            algHeader.tax_code = "0";
                            algHeader.tax_system = "";
                            algDetil.tax_code = "0";
                            algDetil.tax_system = "";
                        }

                        if (algDetil.dim_6.Trim() == "751" || algDetil.dim_6.Trim() == "761") //for all supplier PACC SHIP UK / AVON 
                        {
                            algHeader.tax_code = "ZI";
                            algHeader.tax_system = "";//0 change 28052014
                            algDetil.tax_code = "0";
                            algDetil.tax_system = "";//0 change 28052014
                        }

                        algbatchinputList.Add(algDetil);
                    }

                    string agrtidList = "";
                    foreach (algbatchinput algb in algbatchinputList)
                    {
                        m_AlgBatcInputService.Insert(algb);
                        m_AlgBatcInputService.InsertToInterfaceTable(algb);
                        agrtidList += ";" + algb.agrtid.ToString();
                    }
                    foreach (BunkerPO poItem in po.ItemsAndQuotation)
                    {
                        UpdateStatusPOInterfaceToDone(poItem.po_interface_id, agrtidList, batchID);
                    }
                    //ADDITIONAL INFORMATION PO - QUOTATION///////////////////////
                    int textNo = 1;
                    foreach (BunkerPO poItem in po.ItemsAndQuotation)
                    {
                        string info = poItem.quotation_id + ";" + poItem.supplier_quotation + ";" + poItem.grade + ";" + poItem.price_per_mt +
                           ";" + (poItem.delivery_lumpsum_price == 0 ? "0" : poItem.delivery_lumpsum_price.ToString()) +";" + ((algbatchinput)algbatchinputList[0]).ext_ord_ref;
                        m_ud_algbatchinput_textService.Insert(((algbatchinput)algbatchinputList[0]).client,
                            ((algbatchinput)algbatchinputList[0]).batch_id, ((algbatchinput)algbatchinputList[0]).order_id,
                            ((algbatchinput)algbatchinputList[0]).ext_ord_ref, textNo, info);

                        string short_info = ((algbatchinput)algbatchinputList[0]).ext_ord_ref + ";" +poItem.quotation_id + ";" + poItem.supplier_quotation + ";" + poItem.grade + ";" + poItem.price_per_mt +
                             ";" + (poItem.delivery_lumpsum_price == 0 ? "0" : poItem.delivery_lumpsum_price.ToString());
                        m_apodettextService.Insert(((algbatchinput)algbatchinputList[0]).client, Convert.ToInt64(po.enquiry_no),// ((algbatchinput)algbatchinputList[0]).order_id,
                            textNo, short_info, "DESL", "SP");

                        //m_apodettextService.Insert(((algbatchinput)algbatchinputList[0]).client, Convert.ToInt64(po.enquiry_no), textNo, ((algbatchinput)algbatchinputList[0]).ext_ord_ref, "DESL", "SP");
                        textNo++;
                    }
                    // test++;
                }
                //m_AlgBatcInputService.Insert(new algbatchinput());
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }

        }
        private void UpdateStatusPOInterfaceToDone(int p, string agtid, string batch_id)
        {
            m_dataBaseConectivity.InterfaceSqlConnection_c.Open();
            SqlCommand cmd = new SqlCommand(BunkerPOSQL.UpdatePOTempPickup_flagStatus(), m_dataBaseConectivity.InterfaceSqlConnection_c);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "1";
            cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@pickup_update_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@agrtidList", SqlDbType.NVarChar).Value = agtid;
            cmd.Parameters.Add("@po_interface_id", SqlDbType.Int).Value = p;
            cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = "";
            cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = batch_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection_c.Close();
        }
        private void UpdateRemarks(int p, string remarks, string oldAlert)
        {
            if (remarks == oldAlert) return;
            m_dataBaseConectivity.InterfaceSqlConnection_c.Open();
            SqlCommand cmd = new SqlCommand(BunkerPOSQL.UpdatePOTempPickup_flagStatus(), m_dataBaseConectivity.InterfaceSqlConnection_c);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
            cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@pickup_update_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@agrtidList", SqlDbType.NVarChar).Value = "";
            cmd.Parameters.Add("@po_interface_id", SqlDbType.Int).Value = p;
            cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = remarks;
            cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = "";
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection_c.Close();
        }
        private bool validateSupplier(BunkerPO bunker, AlertType alertType)
        {
            bool valid = bunker.Supplier != null;
            Alert alert = m_alertService.FindAlertByKey(bunker.enquiry_no.Trim() + bunker.supplier.Trim(), alertType);
            if (valid)
            {
                if (alert != null)
                {
                    alert.alert_status = AlertStatus.RESOLVED;
                    m_alertService.UpdateAlertStatus(alert);
                }
            }
            else
            {
                if (alert == null)
                    m_alertService.InsertAlert(new Alert(alertType, string.Format("Unable to find Supplier with name ' {0} ' in asuheader - Agresso for PO Bunker# {1}",
                        bunker.supplier.Trim(), bunker.enquiry_no.Trim()), AlertStatus.PENDING, bunker.enquiry_no.Trim() + bunker.supplier.Trim(), "Supplier"));
                else
                    m_alertService.UpdateAlertStatus(alert);
            }
            return valid;
        }
        private bool validateProfitCentre(BunkerPO bunker, AlertType alertType)
        {
            bool valid = bunker.ProfitCentre != null;
            Alert alert = m_alertService.FindAlertByKey(bunker.enquiry_no.Trim() + bunker.profit_centre_fullname.Trim(), alertType);
            if (valid)
            {
                if (alert != null)
                {
                    alert.alert_status = AlertStatus.RESOLVED;
                    m_alertService.UpdateAlertStatus(alert);
                }
            }
            else
            {
                if (alert == null)
                    m_alertService.InsertAlert(new Alert(alertType, string.Format("Unable to find Profit Centre with name ' {0} ' in agldimvalue - Agresso for PO Bunker# {1} ",
                        bunker.profit_centre_fullname.Trim(), bunker.enquiry_no.Trim()), AlertStatus.PENDING, bunker.enquiry_no.Trim() + bunker.profit_centre_fullname.Trim(), "Profit Centre"));
                else
                    m_alertService.UpdateAlertStatus(alert);
            }
            return valid;
        }
        private bool validateVessel(BunkerPO bunker, AlertType alertType)
        {
            bool valid = bunker.Vessel != null;
            Alert alert = m_alertService.FindAlertByKey(bunker.enquiry_no.Trim() + bunker.vessel.Trim(), alertType);
            if (valid)
            {
                if (alert != null)
                {
                    alert.alert_status = AlertStatus.RESOLVED;
                    m_alertService.UpdateAlertStatus(alert);
                }
            }
            else
            {
                if (alert == null)
                    m_alertService.InsertAlert(new Alert(alertType, string.Format("Unable to find Vessel with name ' {0} ' in agldimvalue - Agresso for PO Bunker# {1}",
                        bunker.vessel.Trim(), bunker.enquiry_no.Trim()), AlertStatus.PENDING, bunker.enquiry_no.Trim() + bunker.vessel.Trim(), "Vessel"));
                else
                    m_alertService.UpdateAlertStatus(alert);
            }
            return valid;
        }
        private int GetSequenceRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_2) + 1;
            runningNumber.val_2 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
        public void ImportAllDataSoftmarToMiddleLayer()
        {
            try
            {
                IList pos = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = BunkerPOSQL.GetAllStemmedBunkerPOAndQutation();
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    BunkerPO po = new BunkerPO(rd, false);
                    po.headerdetail = "H";
                    pos.Add(po);
                }
                rd.Close();

                //--------------------ADDD more by GetPOByEnquiry----
                IList poList = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerPOSQL.GetAllBunkerPOEnquiry();
                cmd.CommandTimeout = 300;
                cmd.Parameters.Clear();
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    poList.Add(rd[0]);
                }
                rd.Close();

                foreach (BunkerPO ci in pos)
                {
                    cmd.Parameters.Clear();
                    if (!poList.Contains(ci.enquiry_no))
                    {
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@headerdetail", SqlDbType.NVarChar).Value = ci.headerdetail;
                        cmd.Parameters.Add("@supplier", SqlDbType.NVarChar).Value = ci.supplier;
                        cmd.Parameters.Add("@supplier_quotation", SqlDbType.NVarChar).Value = ci.supplier_quotation;
                        cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = ci.profit_centre_fullname;
                        cmd.Parameters.Add("@stemmed_date", SqlDbType.DateTime).Value = ci.stemmed_date;
                        cmd.Parameters.Add("@voyage_no", SqlDbType.NVarChar).Value = ci.voyage_no;
                        cmd.Parameters.Add("@quantity_stemmed", SqlDbType.Decimal).Value = ci.quantity_stemmed;
                        cmd.Parameters.Add("@price_gross", SqlDbType.Decimal).Value = ci.price_gross;
                        cmd.Parameters.Add("@expense_broker", SqlDbType.Decimal).Value = ci.expense_broker;
                        cmd.Parameters.Add("@expense_delivery", SqlDbType.Decimal).Value = ci.expense_delivery;
                        cmd.Parameters.Add("@lifting_status", SqlDbType.NVarChar).Value = ci.lifting_status;
                        cmd.Parameters.Add("@due", SqlDbType.DateTime).Value = ci.due;
                        cmd.Parameters.Add("@enquiry_date", SqlDbType.DateTime).Value = ci.enquiry_date;
                        cmd.Parameters.Add("@enquiry_no", SqlDbType.NVarChar).Value = ci.enquiry_no;

                        cmd.Parameters.Add("@vessel", SqlDbType.NVarChar).Value = ci.vessel;
                        cmd.Parameters.Add("@port", SqlDbType.NVarChar).Value = ci.port;
                        cmd.Parameters.Add("@buyer", SqlDbType.NVarChar).Value = ci.buyer;
                        cmd.Parameters.Add("@buyer_remarks", SqlDbType.NVarChar).Value = ci.buyer_remarks;
                        cmd.Parameters.Add("@delivery_date", SqlDbType.DateTime).Value = ci.delivery_date;
                        cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = ci.grade;
                        cmd.Parameters.Add("@delivery_lumpsum_price", SqlDbType.Decimal).Value = ci.delivery_lumpsum_price;
                        cmd.Parameters.Add("@price_per_mt", SqlDbType.Decimal).Value = ci.price_per_mt;
                        cmd.Parameters.Add("@quotation_id", SqlDbType.Int).Value = ci.quotation_id;
                        cmd.Parameters.Add("@quotation_status", SqlDbType.NVarChar).Value = ci.quotation_status;
                        cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;

                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@enquiry_status", SqlDbType.NVarChar).Value = ci.enquiry_status;
                        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = ci.vessel_code;
                        cmd.Parameters.Add("@est_qty_max", SqlDbType.Decimal).Value = ci.est_qty_max;

                        cmd.CommandText = BunkerPOSQL.InsertBunkerPOTemp();
                        cmd.ExecuteNonQuery();
                    }

                    //cmd.CommandText = BunkerPOSQL.CountBunkerPOTemp();
                    //cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    //cmd.Parameters.Clear();
                    //cmd.Parameters.Add("@headerdetail", SqlDbType.NVarChar).Value = ci.headerdetail;
                    //cmd.Parameters.Add("@supplier", SqlDbType.NVarChar).Value = ci.supplier;
                    //cmd.Parameters.Add("@supplier_quotation", SqlDbType.NVarChar).Value = ci.supplier_quotation;
                    //cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = ci.profit_centre_fullname;
                    //cmd.Parameters.Add("@stemmed_date", SqlDbType.DateTime).Value = ci.stemmed_date;
                    //cmd.Parameters.Add("@voyage_no", SqlDbType.NVarChar).Value = ci.voyage_no;
                    //cmd.Parameters.Add("@quantity_stemmed", SqlDbType.Decimal).Value = ci.quantity_stemmed;
                    //cmd.Parameters.Add("@price_gross", SqlDbType.Decimal).Value = ci.price_gross;
                    //cmd.Parameters.Add("@expense_broker", SqlDbType.Decimal).Value = ci.expense_broker;
                    //cmd.Parameters.Add("@expense_delivery", SqlDbType.Decimal).Value = ci.expense_delivery;
                    //cmd.Parameters.Add("@lifting_status", SqlDbType.NVarChar).Value = ci.lifting_status;
                    //cmd.Parameters.Add("@due", SqlDbType.DateTime).Value = ci.due;
                    //cmd.Parameters.Add("@enquiry_date", SqlDbType.DateTime).Value = ci.enquiry_date;
                    //cmd.Parameters.Add("@enquiry_no", SqlDbType.NVarChar).Value = ci.enquiry_no;

                    //cmd.Parameters.Add("@vessel", SqlDbType.NVarChar).Value = ci.vessel;
                    //cmd.Parameters.Add("@port", SqlDbType.NVarChar).Value = ci.port;
                    //cmd.Parameters.Add("@buyer", SqlDbType.NVarChar).Value = ci.buyer;
                    //cmd.Parameters.Add("@buyer_remarks", SqlDbType.NVarChar).Value = ci.buyer_remarks;
                    //cmd.Parameters.Add("@delivery_date", SqlDbType.DateTime).Value = ci.delivery_date;
                    //cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = ci.grade;
                    //cmd.Parameters.Add("@delivery_lumpsum_price", SqlDbType.Decimal).Value = ci.delivery_lumpsum_price;
                    //cmd.Parameters.Add("@price_per_mt", SqlDbType.Decimal).Value = ci.price_per_mt;
                    //cmd.Parameters.Add("@quotation_id", SqlDbType.Int).Value = ci.quotation_id;
                    //cmd.Parameters.Add("@quotation_status", SqlDbType.NVarChar).Value = ci.quotation_status;
                    //cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;
                    //int found = Convert.ToInt16(cmd.ExecuteScalar());
                    //if (found == 0)
                    //{
                    //    cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                    //    cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                    //    cmd.Parameters.Add("@enquiry_status", SqlDbType.NVarChar).Value = ci.enquiry_status;
                    //    cmd.CommandText = BunkerPOSQL.InsertBunkerPOTemp();
                    //    cmd.ExecuteNonQuery();
                    //}
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void ImportAllDataSoftmarToMiddleLayer(int enquiryNumber)
        {
            try
            {
                IList pos = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = BunkerPOSQL.GetAllStemmedBunkerPOAndQutationByEnquiryNumber();
                cmd.Parameters.Add("@enquiry_no", SqlDbType.Int).Value = enquiryNumber;
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    BunkerPO po = new BunkerPO(rd, false);
                    po.headerdetail = "H";
                    pos.Add(po);
                }
                rd.Close();

                //--------------------ADDD more by GetPOByEnquiry----
                IList poList = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerPOSQL.GetAllBunkerPOEnquiry();
                cmd.CommandTimeout = 300;
                cmd.Parameters.Clear();
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    poList.Add(rd[0]);
                }
                rd.Close();

                foreach (BunkerPO ci in pos)
                {
                    cmd.Parameters.Clear();
                    if (!poList.Contains(ci.enquiry_no))
                    {
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@headerdetail", SqlDbType.NVarChar).Value = ci.headerdetail;
                        cmd.Parameters.Add("@supplier", SqlDbType.NVarChar).Value = ci.supplier;
                        cmd.Parameters.Add("@supplier_quotation", SqlDbType.NVarChar).Value = ci.supplier_quotation;
                        cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = ci.profit_centre_fullname;
                        cmd.Parameters.Add("@stemmed_date", SqlDbType.DateTime).Value = ci.stemmed_date;
                        cmd.Parameters.Add("@voyage_no", SqlDbType.NVarChar).Value = ci.voyage_no;
                        cmd.Parameters.Add("@quantity_stemmed", SqlDbType.Decimal).Value = ci.quantity_stemmed;
                        cmd.Parameters.Add("@price_gross", SqlDbType.Decimal).Value = ci.price_gross;
                        cmd.Parameters.Add("@expense_broker", SqlDbType.Decimal).Value = ci.expense_broker;
                        cmd.Parameters.Add("@expense_delivery", SqlDbType.Decimal).Value = ci.expense_delivery;
                        cmd.Parameters.Add("@lifting_status", SqlDbType.NVarChar).Value = ci.lifting_status;
                        cmd.Parameters.Add("@due", SqlDbType.DateTime).Value = ci.due;
                        cmd.Parameters.Add("@enquiry_date", SqlDbType.DateTime).Value = ci.enquiry_date;
                        cmd.Parameters.Add("@enquiry_no", SqlDbType.NVarChar).Value = ci.enquiry_no;

                        cmd.Parameters.Add("@vessel", SqlDbType.NVarChar).Value = ci.vessel;
                        cmd.Parameters.Add("@port", SqlDbType.NVarChar).Value = ci.port;
                        cmd.Parameters.Add("@buyer", SqlDbType.NVarChar).Value = ci.buyer;
                        cmd.Parameters.Add("@buyer_remarks", SqlDbType.NVarChar).Value = ci.buyer_remarks;
                        cmd.Parameters.Add("@delivery_date", SqlDbType.DateTime).Value = ci.delivery_date;
                        cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = ci.grade;
                        cmd.Parameters.Add("@delivery_lumpsum_price", SqlDbType.Decimal).Value = ci.delivery_lumpsum_price;
                        cmd.Parameters.Add("@price_per_mt", SqlDbType.Decimal).Value = ci.price_per_mt;
                        cmd.Parameters.Add("@quotation_id", SqlDbType.Int).Value = ci.quotation_id;
                        cmd.Parameters.Add("@quotation_status", SqlDbType.NVarChar).Value = ci.quotation_status;
                        cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;

                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@enquiry_status", SqlDbType.NVarChar).Value = ci.enquiry_status;
                        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = ci.vessel_code;
                        cmd.Parameters.Add("@est_qty_max", SqlDbType.Decimal).Value = ci.est_qty_max;

                        cmd.CommandText = BunkerPOSQL.InsertBunkerPOTemp();
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList GetAllPendingPendingBunkerPO()
        {
            IList bunkerPO = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerPOSQL.GetAllPendingPendingBunkerPO();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    bunkerPO.Add(new BunkerPO(rd));
                }
                rd.Close();
                return bunkerPO;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public int GetAllPendingPendingBunkerPOCount()
        {
            int bunkerPO = 0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerPOSQL.GetAllPendingPendingBunkerPOCount();
                bunkerPO = Convert.ToInt32(cmd.ExecuteScalar());
                return bunkerPO;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllPendingPendingBunkerPO(string p)
        {
            IList bunkerPO = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerPOSQL.GetAllPendingPendingBunkerPO(p);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    bunkerPO.Add(new BunkerPO(rd));
                }
                rd.Close();
                return bunkerPO;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }


        internal BunkerPO GetPOByEnquiryStemed(int p, string grade) //
        {
            BunkerPO bunkerPO = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerPOSQL.GetAllBunkerPOByEnquiryNumber();
                cmd.Parameters.Add("@enquiry_no", SqlDbType.Int).Value = p;
                cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = grade;
                //cmd.Parameters.Add("@supplier", SqlDbType.NVarChar).Value = supplier;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    bunkerPO = new BunkerPO(rd, true);
                }
                rd.Close();
                return bunkerPO;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void FixAppodetext()//DONE---19112013
        {
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                cmd.CommandText = BunkerPOSQL.GetAllAgressoPO();
                IList listAgressoPO = new ArrayList();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    listAgressoPO.Add(new apoheader(rd));
                }
                rd.Close();

                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerPOSQL.GetPOByExtOrdRef();
                IList listSoftmarPO = new ArrayList();
                foreach (apoheader apoh in listAgressoPO)
                {
                    
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@stemmed_date", SqlDbType.DateTime).Value = apoh.order_date;
                    cmd.Parameters.Add("@ext_ord_ref", SqlDbType.NVarChar).Value = apoh.ext_ord_ref;
                    rd = cmd.ExecuteReader();
                    BunkerPO poHeader = null;
                    while (rd.Read())
                    {
                        BunkerPO podetil = new BunkerPO(rd, true);
                        if (poHeader == null) 
                            poHeader = podetil;
                        poHeader.ItemsAndQuotation.Add(podetil);
                    }
                    rd.Close();
                   
                    if (poHeader != null)
                    {
                        if (!m_apodettextService.IsExistOrderID(Convert.ToInt64(poHeader.enquiry_no)))
                        {
                            int textNo = 1;
                            foreach (BunkerPO poItem in poHeader.ItemsAndQuotation)
                            {
                                string info = poItem.quotation_id + ";" + poItem.supplier_quotation + ";" + poItem.grade + ";" + poItem.price_per_mt +
                                   ";" + (poItem.delivery_lumpsum_price == 0 ? "0" : poItem.delivery_lumpsum_price.ToString()) + ";" + apoh.ext_ord_ref;
                                m_apodettextService.Insert(apoh.client, Convert.ToInt32(poHeader.enquiry_no),// ((algbatchinput)algbatchinputList[0]).order_id,
                                    textNo, info, "DESL", "SP");
                                textNo++;
                            }
                        }
                    }
                }
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
           
        }

        public void UpdateApodettextOrderID()
        {
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                cmd.CommandText = apodettextSQL.GetPendingApodettext();
                IList pendingApodettect = new ArrayList();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    pendingApodettect.Add(new apodettext(rd));
                }
                rd.Close();
                cmd.CommandText = apoheaderSQL.GetAPOheaderByExternalOrdRef();
                foreach (apodettext apodettext in pendingApodettect)
                {
                    string[] shortinfo = apodettext.short_info.Split(';');
                    if (shortinfo.Length < 6) continue;
                    string ext_ord_ref = shortinfo[0];//change to first semicolon 23JAN2013
                    apoheader apoh = null;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@ext_ord_ref", SqlDbType.NVarChar).Value = ext_ord_ref;
                    rd = cmd.ExecuteReader();
                    while (rd.Read())
                        apoh = new apoheader(rd);
                    rd.Close();
                    if (apoh == null) continue;
                    if (!m_apodettextService.IsExistOrderID(apoh.order_id, apodettext.client, apodettext.line_no, apodettext.sequence_no))
                        m_apodettextService.UpdateOrderIDandStatus(apodettext.agrtid, apoh.order_id, "1");
                }
            }
            catch(Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }

        public void UpdateVesselCode()
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = "select * from i_bunker_po_temp where pickup_flag != 0";
                SqlCommand cmdSoftmar = new SqlCommand();
                cmdSoftmar.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new BunkerPO(rd, true));
                }
                rd.Close();
                foreach (BunkerPO st in commissionInvoices)
                {
                    cmdSoftmar.CommandText = @"select distinct 
                    vVMVoyageSrch.Vessel vessel_code                    
                    from 
                    vBSEnquirySearch                   
                    LEFT OUTER JOIN vVMVoyageSrch ON vBSEnquirySearch.VMVOYAGE = vVMVoyageSrch.ID                    
                    where                                              
                    vBSEnquirySearch.EnquiryID = " + st.enquiry_no;
                    string vessel_code = "";
                    SqlDataReader rdSf = cmdSoftmar.ExecuteReader();
                    while (rdSf.Read())
                    {
                        vessel_code = rdSf[0].ToString();
                    }
                    rdSf.Close();
                    cmd.CommandText = string.Format("update i_bunker_po_temp set vessel_code = '{0}'  where po_interface_id = {1}", vessel_code, st.po_interface_id);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
        }
        public void UpdateEstQtyMax()
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = "select * from i_bunker_po_temp where pickup_flag in (0,1)";
                SqlCommand cmdSoftmar = new SqlCommand();
                cmdSoftmar.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new BunkerPO(rd, true));
                }
                rd.Close();
                foreach (BunkerPO st in commissionInvoices)
                {
                    cmdSoftmar.CommandText = @"select max(EstQtyMax) from vBSEnquiryBunkerLifting where Enquiry = " + st.enquiry_no + " and Quality = '" + st.grade + "'";
                    decimal estMxQty = 0;
                    object val = cmdSoftmar.ExecuteScalar();
                    if(!(val is DBNull) )
                        estMxQty = Convert.ToDecimal(val);
                    cmd.CommandText = string.Format("update i_bunker_po_temp set est_qty_max = {0}  where po_interface_id = {1}", estMxQty, st.po_interface_id);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
        }
    }
}
